package com.jxd.petMgr.dao.impl;

import com.jxd.petMgr.dao.IPetDao;
import com.jxd.petMgr.model.Pet;
import com.jxd.petMgr.util.PageUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author lzh
 * @version 1.0
 * @Description
 * @date 2021/11/3
 */


public class PetDaoImpl extends BaseDao implements IPetDao {
    /**
     * 根据主人id和分页信息获得宠物
     * @param pageUtil
     * @param petOwnerId
     * @return 宠物对象集合
     */
    @Override
    public List<Pet> getPetByPetOwnerId(PageUtil pageUtil,int petOwnerId) {
        List<Pet> list = new ArrayList<>();
        conn = getConn();
        String sql = "select id, name , typename, health, love , birthday, owner_id, name ,store_id , price from pet " +
                "where owner_id = ? limit ?,?";

        try {
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,petOwnerId);
            pstm.setInt(2,pageUtil.getStart());
            pstm.setInt(3,pageUtil.getLimit());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Pet pet = new Pet(rs.getInt("id"), rs.getString("name"),
                        rs.getString("typename"), rs.getInt("health"),
                        rs.getInt("love"), rs.getString("birthday"),
                        rs.getInt("owner_id"), rs.getInt("store_id"),
                        rs.getDouble("price"));
                list.add(pet);
//                Map<String,Object> map = new HashMap<>();
//                map.put("id",rs.getInt("id"));
//                map.put("name",rs.getString("name"));
//                map.put("typename",rs.getString("typename"));
//                map.put("health",rs.getInt("health"));
//                map.put("love",rs.getInt("love"));
//                map.put("birthday",rs.getString("birthday"));
//                map.put("owner_id",rs.getInt("owner_id"));
//                map.put("price",rs.getDouble("price"));
//                map.put("shopName",rs.getString("name(1)"));
//                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, pstm, rs);
        }
        return list;
    }

    /**
     * 根据主人id获取主人宠物的数量
     *
     * @param petOwnerId
     * @return
     */
    @Override
    public int getPetByPetOwnerIdCount(int petOwnerId) {
        int count = 0;
        conn = getConn();
        String sql = "select count(id) from pet " +
                "where owner_id = ?";

        try {
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,petOwnerId);
            rs = pstm.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, pstm, rs);
        }
        return count;
    }

    /**
     * 得到所有宠物对象
     *
     * @return 宠物对象集合
     */
    @Override
    public List<Map<String, Object>> getAllPetHaveShopName(PageUtil pageUtil,Map<String,String> mapInfo) {
        List<Map<String,Object>> list = new ArrayList<>();
        conn = getConn();
        String priceMin = mapInfo.get("priceMin");
        String priceMax = mapInfo.get("priceMax");
        String typeName = mapInfo.get("typeName");
        String sql = "select p.id, p.name , p.typename, p.health, p.love , p.birthday, p.owner_id, s.storename , p.price from pet p\n" +
                "join petstore s on p.store_id = s.id where 1=1";
        StringBuffer sb = new StringBuffer(sql);
        if (priceMin != null && !"".equals(priceMin)){
            sb.append(" and price > "+mapInfo.get("priceMin"));
        }
        if (priceMax != null && !"".equals(priceMax)){
            sb.append(" and price < "+priceMax);
        }
        if (typeName != null && !"".equals(typeName)){
            sb.append(" and p.typename = '"+typeName+"'");
        }
        sb.append(" limit ?,?");

        try {
            pstm = conn.prepareStatement(sb.toString());
            pstm.setInt(1,pageUtil.getStart());
            pstm.setInt(2,pageUtil.getLimit());
            rs = pstm.executeQuery();
            while (rs.next()) {
//                Pet pet = new Pet(rs.getInt("id"), rs.getString("name"),
//                        rs.getString("typename"), rs.getInt("health"),
//                        rs.getInt("love"), rs.getString("birthday"),
//                        rs.getInt("owner_id"), rs.getInt("store_id"),
//                        rs.getDouble("price"));
//                list.add(pet);
                Map<String,Object> map = new HashMap<>();
                map.put("id",rs.getInt("id"));
                map.put("name",rs.getString("name"));
                map.put("typename",rs.getString("typename"));
                map.put("health",rs.getInt("health"));
                map.put("love",rs.getInt("love"));
                map.put("birthday",rs.getString("birthday"));
                map.put("owner_id",rs.getInt("owner_id"));
                map.put("price",rs.getDouble("price"));
                map.put("shopName",rs.getString("storename"));
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, pstm, rs);
        }
        return list;
    }

    /**
     * 获得所有有宠物商店名的宠物的数量
     *
     * @return
     */
    @Override
    public int getAllPetHaveShopNameCount(Map<String,String> mapInfo) {
        List<Map<String,Object>> list = new ArrayList<>();
        int count = 0;
        conn = getConn();
        String priceMin = mapInfo.get("priceMin");
        String priceMax = mapInfo.get("priceMax");
        String typeName = mapInfo.get("typeName");
        String sql = "select count(p.id) from pet p\n" +
                "join petstore s on p.store_id = s.id where 1=1";
        StringBuffer sb = new StringBuffer(sql);
        if (priceMin != null && !"".equals(priceMin)){
            sb.append(" and price > "+priceMin);
        }
        if (priceMax != null && !"".equals(priceMax)){
            sb.append(" and price < "+priceMax);
        }
        if (typeName != null && !"".equals(typeName)){
            sb.append(" and p.typename = '"+typeName+"'");
        }
        try {
            pstm = conn.prepareStatement(sb.toString());
            rs = pstm.executeQuery();
            if (rs.next()){
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 插入宠物对象，返回是否插入成功
     *
     * @param pet  宠物对象
     * @param conn
     * @param pstm
     * @return 是否插入成功
     */
    @Override
    public boolean insertPet(Pet pet, Connection conn, PreparedStatement pstm) throws SQLException {
        boolean result;
        String sql = "insert into pet(name, typename, health, love, birthday, owner_id, store_id,price) " +
                "values(?,?,?,?,(select current_date from dual),?,?,?)";
        pstm = conn.prepareStatement(sql);
        pstm.setString(1, pet.getName());
        pstm.setString(2, pet.getTypename());
        pstm.setInt(3, pet.getHealth());
        pstm.setInt(4, pet.getLove());
        pstm.setInt(5, pet.getOwner_id());
        pstm.setInt(6, pet.getStore_id());
        pstm.setDouble(7, pet.getPrice());
        result = pstm.executeUpdate() > 0;
        return result;
    }



    /**
     * 传入宠物对象，更新宠物表
     *
     * @param pet 宠物对象
     * @return 是否更新成功
     */
    @Override
    public boolean updatePet(Pet pet, Connection conn,PreparedStatement pstm) throws SQLException {
        boolean result;
        String sql = "update pet set name = ?, typename = ?, health = ?, love = ?, birthday = ?, owner_id = ? , " +
                "store_id = ? , price = ? where id = ?";
        pstm = conn.prepareStatement(sql);
        pstm.setString(1, pet.getName());
        pstm.setString(2, pet.getTypename());
        pstm.setInt(3, pet.getHealth());
        pstm.setInt(4, pet.getLove());
        pstm.setString(5, pet.getBirthday());
        pstm.setInt(6, pet.getOwner_id());
        pstm.setInt(7, pet.getStore_id());
        pstm.setDouble(8, pet.getPrice());
        pstm.setInt(9, pet.getId());
        result = pstm.executeUpdate() > 0;
        return result;
    }

    /**
     * 批量更新宠物主人
     *
     * @param petIds  宠物id集合
     * @param ownerId 主人id
     * @return 是否更新成功
     */
    @Override
    public boolean batchUpdate(List<Integer> petIds, int ownerId ,int storeId ,Connection conn,PreparedStatement pstm) throws SQLException {
        boolean result = false;
        String sql = "update pet set owner_id = ? ,store_id = ? where id in ( ";
        StringBuffer bs = new StringBuffer(sql);
        for (int i = 0; i < petIds.size() - 1; i++) {
            bs.append(" "+petIds.get(i)+",");
        }
        //最后一个id
        bs.append(" "+petIds.get(petIds.size() -1)+")");
        pstm = conn.prepareStatement(bs.toString());
        pstm.setInt(1,ownerId);
        pstm.setInt(2,storeId);
        result = pstm.executeUpdate() == petIds.size();
        return result;
    }

    /**
     * 获得宠物的所有种类
     *
     * @return 宠物种类集合
     */
    @Override
    public List<String> getAllTypeName() {
        conn = getConn();
        List<String> list = new ArrayList<>();
        String sql = "select DISTINCT typename FROM pet";
        try {
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()){
                list.add(rs.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn,pstm,rs);
        }
        return list;
    }

    /**
     * 根据宠物id获得宠物对象
     *
     * @param id 宠物id
     * @return 宠物对象
     */
    @Override
    public Pet getPetById(int id) {
        Pet pet = null;
        conn = getConn();
        String sql = "select id, name , typename, health, love , birthday, owner_id, name ,store_id , price from pet where id = ?";

        try {
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,id);
            rs = pstm.executeQuery();
            while (rs.next()) {
                pet = new Pet(rs.getInt("id"), rs.getString("name"),
                        rs.getString("typename"), rs.getInt("health"),
                        rs.getInt("love"), rs.getString("birthday"),
                        rs.getInt("owner_id"), rs.getInt("store_id"),
                        rs.getDouble("price"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, pstm, rs);
        }
        return pet;
    }

}
